using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    public class TotalCostAccounting
    {
        public long ID = -1;
        public long CostingID = 0;
        public long CostAccountingID = 0;
        public double TotalBuyInvoice = 0;
        public double TotalComAgent = 0;
        public double TotalTaxAgent = 0;
        public double TotalSellInvoice = 0;
        public double TotalComCustomer = 0;
        public double TotalTaxCustomer = 0;
        public string Receivables_Note = "";
        public string Payables_Note = "";

        public double Receivables_Obtained = 0;
        public double Receivables_Paid_Com = 0;
        public double Payables_Payment = 0;
        public double Payables_Paid_Com = 0;

        public double Receivables_Must_Obtained = 0;
        public double Receivables_Must_Paid_Com = 0;
        public double Payables_Must_Payment = 0;
        public double Payables_Must_Paid_Com = 0;

        public string Receivables_PaymentMethod = "";
        public string Payables_PaymentMethod = "";
        public DateTime Receivables_Update = DateTime.MinValue;
        public DateTime Payables_Update = DateTime.MinValue;
        public DateTime LastUpdate = DateTime.Now;

        public int Payables_Must_Payment_Verify = 0;
        public int Payables_Must_Paid_Com_Verify = 0;
        public int Receivables_Must_Obtained_Verify = 0;
        public int Receivables_Must_Paid_Com_Verify = 0;
        public int Receivables_CloseFile = 0;
        public int Payables_CloseFile = 0;

        public TotalCostAccounting()
        {
        }

        public TotalCostAccounting(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_total_cost_accounting";

            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "INSERT INTO " + strTableName +
                    "(CostingID, " +
                    "CostAccountingID," +
                    "TotalBuyInvoice," +
                    "TotalComAgent," +
                    "TotalTaxAgent," +
                    "TotalSellInvoice," +
                    "TotalComCustomer," +
                    "TotalTaxCustomer," +
                    "Receivables_Note," +
                    "Payables_Note," +
                    "Receivables_Obtained," +
                    "Receivables_Paid_Com," +
                    "Payables_Payment," +
                    "Payables_Paid_Com," +
                    "Receivables_Must_Obtained," +
                    "Receivables_Must_Paid_Com," +
                    "Payables_Must_Payment," +
                    "Payables_Must_Paid_Com," +
                    "Receivables_PaymentMethod," +
                    "Payables_PaymentMethod," +
                    "Receivables_Update," +
                    "Payables_Update," +
                    "Payables_Must_Payment_Verify," +
                    "Payables_Must_Paid_Com_Verify," +
                    "Receivables_Must_Obtained_Verify," +
                    "Receivables_Must_Paid_Com_Verify," +
                    "LastUpdate" +
                    ",Receivables_CloseFile" +
                    ",Payables_CloseFile" +
                    ")" +
                    "VALUES" +
                    "(" +
                    CostingID.ToString() + "," +
                    CostAccountingID.ToString() + "," +
                    TotalBuyInvoice.ToString() + "," +
                    TotalComAgent.ToString() + "," +
                    TotalTaxAgent.ToString() + "," +
                    TotalSellInvoice.ToString() + "," +
                    TotalComCustomer.ToString() + "," +
                    TotalTaxCustomer.ToString() + "," +
                    "'" + DBModule.RefineString(Receivables_Note) + "'," +
                    "'" + DBModule.RefineString(Payables_Note) + "'," +
                    Receivables_Obtained.ToString() + "," +
                    Receivables_Paid_Com.ToString() + "," +
                    Payables_Payment.ToString() + "," +
                    Payables_Paid_Com.ToString() + "," +
                    Receivables_Must_Obtained.ToString() + "," +
                    Receivables_Must_Paid_Com.ToString() + "," +
                    Payables_Must_Payment.ToString() + "," +
                    Payables_Must_Paid_Com.ToString() + "," +
                    "'" + DBModule.RefineString(Receivables_PaymentMethod) + "'," +
                    "'" + DBModule.RefineString(Payables_PaymentMethod) + "'," +
                    DBModule.RefineDatetime(Receivables_Update) + "," +
                    DBModule.RefineDatetime(Payables_Update) + "," +
                    Payables_Must_Payment_Verify.ToString() + "," +
                    Payables_Must_Paid_Com_Verify.ToString() + "," +
                    Receivables_Must_Obtained_Verify.ToString() + "," +
                    Receivables_Must_Paid_Com_Verify.ToString() + "," +
                    DBModule.RefineDatetime(LastUpdate) +
                    "," + Receivables_CloseFile.ToString() +
                    "," + Payables_CloseFile.ToString() +
                    ")";
                #region DateTime Null Value
                ////Receivables_Update.HasValue ? DBModule.RefineDatetime(Receivables_Update.Value) : DBNull.Value + "," +
                //if (Receivables_Update.HasValue)
                //    strSQL += "," + DBModule.RefineDatetime(Receivables_Update.Value);
                //else
                //    strSQL += "," + DBNull.Value;
                //if (Payables_Update.HasValue)
                //    strSQL += "," + DBModule.RefineDatetime(Payables_Update.Value);
                //else
                //    strSQL += "," + DBNull.Value;
                #endregion
                // run SQL statement                
                //GV.Response.Write(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);

                //UserObjectsLog.SaveObjectActionLog("CostAccountingDetail", "add", cn, trans);
            }
            else // edit object
            {
                // build SQL statement

                strSQL = "Update " + strTableName + " set " +
                    "CostingID = " + CostingID.ToString() + "," +
                    "CostAccountingID = " + CostAccountingID.ToString() + "," +
                    "TotalBuyInvoice = " + TotalBuyInvoice.ToString() + "," +
                    "TotalComAgent = " + TotalComAgent.ToString() + "," +
                    "TotalTaxAgent = " + TotalTaxAgent.ToString() + "," +
                    "TotalSellInvoice = " + TotalSellInvoice.ToString() + "," +
                    "TotalComCustomer = " + TotalComCustomer.ToString() + "," +
                    "TotalTaxCustomer = " + TotalTaxCustomer.ToString() + "," +
                    "Receivables_Obtained = " + Receivables_Obtained.ToString() + "," +
                    "Receivables_Paid_Com = " + Receivables_Paid_Com.ToString() + "," +
                    "Payables_Payment = " + Payables_Payment.ToString() + "," +
                    "Payables_Paid_Com = " + Payables_Paid_Com.ToString() + "," +
                    "Receivables_Must_Obtained = " + Receivables_Must_Obtained.ToString() + "," +
                    "Receivables_Must_Paid_Com = " + Receivables_Must_Paid_Com.ToString() + "," +
                    "Payables_Must_Payment = " + Payables_Must_Payment.ToString() + "," +
                    "Payables_Must_Paid_Com = " + Payables_Must_Paid_Com.ToString() + "," +
                    "Receivables_Note = '" + DBModule.RefineString(Receivables_Note) + "'," +
                    "Payables_Note = '" + DBModule.RefineString(Payables_Note) + "'," +
                    "Receivables_PaymentMethod = '" + DBModule.RefineString(Receivables_PaymentMethod) + "'," +
                    "Payables_PaymentMethod = '" + DBModule.RefineString(Payables_PaymentMethod) + "'," +
                    "Receivables_Update=" + DBModule.RefineDatetime(Receivables_Update) + "," +
                    "Payables_Update=" + DBModule.RefineDatetime(Payables_Update) + "," +
                    "Payables_Must_Payment_Verify = " + Payables_Must_Payment_Verify.ToString() + "," +
                    "Payables_Must_Paid_Com_Verify = " + Payables_Must_Paid_Com_Verify.ToString() + "," +
                    "Receivables_Must_Obtained_Verify = " + Receivables_Must_Obtained_Verify.ToString() + "," +
                    "Receivables_Must_Paid_Com_Verify = " + Receivables_Must_Paid_Com_Verify.ToString() + "," +
                    "LastUpdate=" + DBModule.RefineDatetime(LastUpdate) +
                    "," + "Receivables_CloseFile = " + Receivables_CloseFile.ToString() +
                    "," + "Payables_CloseFile = " + Payables_CloseFile.ToString() +
                " where ID=" + ID.ToString();
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //UserObjectsLog.SaveObjectActionLog("CostAccountingDetail", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_total_cost_accounting WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            //UserObjectsLog.SaveObjectActionLog("CostAccountingDetail", "delete", cn, trans);
        }
        public static void DoReceivablesCloseFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_total_cost_accounting set Receivables_CloseFile=1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static void DoReceivablesOpenFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_total_cost_accounting set Receivables_CloseFile=0";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static int GetCountReceivablesStatusFile(int StatusFile, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string sqlCommand = "Select Count(ID) From  tbl_total_cost_accounting where Receivables_CloseFile=" + StatusFile;
            string Status = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (Status != null) retVal = int.Parse(Status);
            return retVal;
        }
        public static void DoPayablesCloseFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_total_cost_accounting set Payables_CloseFile=1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static void DoPayablesOpenFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_total_cost_accounting set Payables_CloseFile=0";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static int GetCountPayablesStatusFile(int StatusFile, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string sqlCommand = "Select Count(ID) From  tbl_total_cost_accounting where Payables_CloseFile=" + StatusFile;
            string Status = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (Status != null) retVal = int.Parse(Status);
            return retVal;
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_total_cost_accounting WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("CostingID")) CostingID = long.Parse(dr["CostingID"].ToString());
                if (!dr.IsNull("CostAccountingID")) CostAccountingID = long.Parse(dr["CostAccountingID"].ToString());
                if (!dr.IsNull("TotalBuyInvoice")) TotalBuyInvoice = double.Parse(dr["TotalBuyInvoice"].ToString());
                if (!dr.IsNull("TotalComAgent")) TotalComAgent = double.Parse(dr["TotalComAgent"].ToString());
                if (!dr.IsNull("TotalTaxAgent")) TotalTaxAgent = double.Parse(dr["TotalTaxAgent"].ToString());
                if (!dr.IsNull("TotalSellInvoice")) TotalSellInvoice = double.Parse(dr["TotalSellInvoice"].ToString());
                if (!dr.IsNull("TotalComCustomer")) TotalComCustomer = double.Parse(dr["TotalComCustomer"].ToString());
                if (!dr.IsNull("TotalTaxCustomer")) TotalTaxCustomer = double.Parse(dr["TotalTaxCustomer"].ToString());
                if (!dr.IsNull("Receivables_Note")) Receivables_Note = dr["Receivables_Note"].ToString();
                if (!dr.IsNull("Payables_Note")) Payables_Note = dr["Payables_Note"].ToString();
                if (!dr.IsNull("Receivables_Obtained")) Receivables_Obtained = double.Parse(dr["Receivables_Obtained"].ToString());
                if (!dr.IsNull("Receivables_Paid_Com")) Receivables_Paid_Com = double.Parse(dr["Receivables_Paid_Com"].ToString());
                if (!dr.IsNull("Payables_Payment")) Payables_Payment = double.Parse(dr["Payables_Payment"].ToString());
                if (!dr.IsNull("Payables_Paid_Com")) Payables_Paid_Com = double.Parse(dr["Payables_Paid_Com"].ToString());

                if (!dr.IsNull("Receivables_Must_Obtained")) Receivables_Must_Obtained = double.Parse(dr["Receivables_Must_Obtained"].ToString());
                if (!dr.IsNull("Receivables_Must_Paid_Com")) Receivables_Must_Paid_Com = double.Parse(dr["Receivables_Must_Paid_Com"].ToString());
                if (!dr.IsNull("Payables_Must_Payment")) Payables_Must_Payment = double.Parse(dr["Payables_Must_Payment"].ToString());
                if (!dr.IsNull("Payables_Must_Paid_Com")) Payables_Must_Paid_Com = double.Parse(dr["Payables_Must_Paid_Com"].ToString());

                if (!dr.IsNull("Receivables_PaymentMethod")) Receivables_PaymentMethod = dr["Receivables_PaymentMethod"].ToString();
                if (!dr.IsNull("Payables_PaymentMethod")) Payables_PaymentMethod = dr["Payables_PaymentMethod"].ToString();
                if (!dr.IsNull("Receivables_Update")) Receivables_Update = DateTime.Parse(dr["Receivables_Update"].ToString());
                if (!dr.IsNull("Payables_Update")) Payables_Update = DateTime.Parse(dr["Payables_Update"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["Lastupdate"].ToString());

                if (!dr.IsNull("Payables_Must_Payment_Verify")) Payables_Must_Payment_Verify = int.Parse(dr["Payables_Must_Payment_Verify"].ToString());
                if (!dr.IsNull("Payables_Must_Paid_Com_Verify")) Payables_Must_Paid_Com_Verify = int.Parse(dr["Payables_Must_Paid_Com_Verify"].ToString());
                if (!dr.IsNull("Receivables_Must_Obtained_Verify")) Receivables_Must_Obtained_Verify = int.Parse(dr["Receivables_Must_Obtained_Verify"].ToString());
                if (!dr.IsNull("Receivables_Must_Paid_Com_Verify")) Receivables_Must_Paid_Com_Verify = int.Parse(dr["Receivables_Must_Paid_Com_Verify"].ToString());

                if (!dr.IsNull("Receivables_CloseFile")) Receivables_CloseFile = int.Parse(dr["Receivables_CloseFile"].ToString());
                if (!dr.IsNull("Payables_CloseFile")) Payables_CloseFile = int.Parse(dr["Payables_CloseFile"].ToString());
            }
        }
        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement

            strSQL = "Select tbl_Total_cost_accounting.*," +
                " tbl_cost_accounting.FileNumber," +
                " CONVERT(SUBSTRING_INDEX(tbl_cost_accounting.FileNumber,'-',-1),UNSIGNED INTEGER) AS numFileNumber," +
                " tbl_cost_accounting.Customer," +
                " tbl_cost_accounting.POL," +
                " tbl_cost_accounting.POD," +
                " tbl_cost_accounting.ETD," +
                " tbl_cost_accounting.Liabilities," +
                " tbl_cost_accounting.Agent" +
                " From tbl_Total_cost_accounting,tbl_cost_accounting" +
                " where tbl_Total_cost_accounting.CostAccountingID=tbl_cost_accounting.ID";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void GetListReceivedCurrent(string strCondition, DateTime EndTime, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, out DataSet dsTotal, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            strSQL = "DROP FUNCTION IF EXISTS  ReceivedValue";
            DBModule.ExecuteNonQuery(strSQL, GV.cn, GV.trans);
            strSQL = @"CREATE FUNCTION ReceivedValue(inputFileNumber CHAR(250),inputEndDate DATETIME)
                    RETURNS DOUBLE
                    BEGIN
                        DECLARE dValue DOUBLE;
                        SET dValue= (SELECT  SUM(CashVND_Thu+BankVND_Thu) AS TotalAmountReceive FROM tbl_funds WHERE FileNumber=inputFileNumber 
		                AND ReciprocalAccount_Debt=131 AND SubjectType=1  AND CreateDate <= inputEndDate );
	                IF  ISNULL(dvalue) THEN
		                SET dValue=0;	 
	                END IF;	
                        RETURN dValue;
                    END";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            strSQL = "DROP FUNCTION IF EXISTS  ReceivedComValue";
            DBModule.ExecuteNonQuery(strSQL, GV.cn, GV.trans);
            strSQL = @"CREATE FUNCTION ReceivedComValue(inputFileNumber CHAR(250),inputEndDate DATETIME)
                    RETURNS DOUBLE
                    BEGIN
                        DECLARE dValue DOUBLE;
                        SET dValue= (SELECT  SUM(CashVND_Chi+BankVND_Chi) AS TotalAmountComReceive FROM tbl_funds WHERE FileNumber=inputFileNumber 
		                AND ReciprocalAccount_Had=338 AND SubjectType=5  AND CreateDate <= inputEndDate);
	                IF  ISNULL(dvalue) THEN
		                SET dValue=0;	 
	                END IF;	
                        RETURN dValue;
                    END";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            strSQL = "Select tbl_Total_cost_accounting.*," +
             " tbl_cost_accounting.FileNumber," +
             " CONVERT(SUBSTRING_INDEX(tbl_cost_accounting.FileNumber,'-',-1),UNSIGNED INTEGER) AS numFileNumber," +
             " tbl_cost_accounting.Customer," +
             " tbl_cost_accounting.POL," +
             " tbl_cost_accounting.POD," +
             " tbl_cost_accounting.ETD," +
             " tbl_cost_accounting.Liabilities," +
             " tbl_cost_accounting.Agent," +
                //Current
             " ReceivedValue(tbl_cost_accounting.FileNumber,'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "') as Receivables_Obtained_Current" +
             " ,ReceivedComValue(tbl_cost_accounting.FileNumber,'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "') as Receivables_Paid_Com_Current" +
             " From tbl_Total_cost_accounting,tbl_cost_accounting" +
             " where tbl_Total_cost_accounting.CostAccountingID=tbl_cost_accounting.ID";

            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }

            strSQL = "Select * " +
                ",(TotalSellInvoice - Receivables_Obtained_Current) as Receivables_Must_Obtained_Current " + //So tien con phai thu den thoi diem tim kiem
                ",(TotalComCustomer - TotalTaxCustomer - Receivables_Paid_Com_Current) as Receivables_Must_Paid_Com_Current " + //Com Khach hang con phai thu den thoi diem tim kiem
                "from (" + strSQL + ") as ReceivedCurrent";
            string strSQLTotal = "SELECT  SUM(TotalSellInvoice) AS SUM_Ref_Receivables_Must_Obtained" +
                ",SUM(Receivables_Must_Obtained_Current) AS SUM_Receivables_Must_Obtained" +
                ",SUM(TotalComCustomer) AS SUM_Ref_Receivables_Must_Paid_Com" +
                ",SUM(Receivables_Obtained_Current) AS SUM_Receivables_Obtained" +
                ",SUM(Receivables_Paid_Com_Current) AS SUM_Receivables_Paid_Com" +
                ",SUM(Receivables_Must_Paid_Com_Current) AS SUM_Receivables_Must_Paid_Com" +
                ",SUM(TotalTaxCustomer) AS SUM_Receivables_Tax_Customer " +
                "from (" + strSQL + ") as ReceivedCurrentTotal";
            if ((OrderBy != null) && (OrderBy != ""))
            {
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            }

            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
            {
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            }
            //Total
            dsTotal = null;
            dsTotal = DBModule.ExecuteQuery(strSQLTotal, cn, trans);
        }
        public static void GetListPayablesCurrent(string strCondition, DateTime EndTime, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, out DataSet dsTotal, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            strSQL = "DROP FUNCTION IF EXISTS  PayablesValue";
            DBModule.ExecuteNonQuery(strSQL, GV.cn, GV.trans);
            strSQL = @"CREATE FUNCTION PayablesValue(inputFileNumber CHAR(250),inputEndDate DATETIME)
                    RETURNS DOUBLE
                    BEGIN
                        DECLARE dValue DOUBLE;
                        SET dValue= (SELECT  SUM(CashVND_Chi+BankVND_Chi) AS TotalAmountPayables FROM tbl_funds WHERE FileNumber=inputFileNumber 
		                AND ReciprocalAccount_Had=331 AND SubjectType=3  AND CreateDate <= inputEndDate );
	                IF  ISNULL(dvalue) THEN
		                SET dValue=0;	 
	                END IF;	
                        RETURN dValue;
                    END";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            strSQL = "DROP FUNCTION IF EXISTS  PayablesComValue";
            DBModule.ExecuteNonQuery(strSQL, GV.cn, GV.trans);
            strSQL = @"CREATE FUNCTION PayablesComValue(inputFileNumber CHAR(250),inputEndDate DATETIME)
                    RETURNS DOUBLE
                    BEGIN
                        DECLARE dValue DOUBLE;
                        SET dValue= (SELECT  SUM(CashVND_Chi+BankVND_Chi) AS TotalAmountComPayables FROM tbl_funds WHERE FileNumber=inputFileNumber 
		                AND ReciprocalAccount_Had=338 AND SubjectType=4  AND CreateDate <= inputEndDate );
	                IF  ISNULL(dvalue) THEN
		                SET dValue=0;	 
	                END IF;	
                        RETURN dValue;
                    END";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            strSQL = "Select tbl_Total_cost_accounting.*," +
             " tbl_cost_accounting.FileNumber," +
             " CONVERT(SUBSTRING_INDEX(tbl_cost_accounting.FileNumber,'-',-1),UNSIGNED INTEGER) AS numFileNumber," +
             " tbl_cost_accounting.Customer," +
             " tbl_cost_accounting.POL," +
             " tbl_cost_accounting.POD," +
             " tbl_cost_accounting.ETD," +
             " tbl_cost_accounting.Liabilities," +
             " tbl_cost_accounting.Agent," +
                //Current
             " PayablesValue(tbl_cost_accounting.FileNumber,'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "') as Payables_Payment_Current" +
             " ,PayablesComValue(tbl_cost_accounting.FileNumber,'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "') as Payables_Paid_Com_Current" +
             " From tbl_Total_cost_accounting,tbl_cost_accounting" +
             " where tbl_Total_cost_accounting.CostAccountingID=tbl_cost_accounting.ID";

            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }

            strSQL = "Select * " +
                ",(TotalBuyInvoice - Payables_Payment_Current) as Payables_Must_Payment_Current " + //So tien con phai tra nha cung cap
                ",(TotalComAgent - TotalTaxAgent - Payables_Paid_Com_Current) as Payables_Must_Paid_Com_Current " + //Com nha cung cap con phai tra
                "from (" + strSQL + ") as PayablesCurrent";
            string strSQLTotal = "SELECT   SUM(TotalBuyInvoice) AS SUM_Ref_Payables_Must_Payment" +
                " ,SUM(Payables_Must_Payment_Current) AS SUM_Payables_Must_Payment" +
                ",SUM(TotalComAgent) AS SUM_Ref_Payables_Must_Paid_Com" +
                ",SUM(Payables_Payment_Current) AS SUM_Payables_Payment" +
                ",SUM(Payables_Paid_Com_Current) AS SUM_Payables_Paid_Com" +
                ",SUM(Payables_Must_Paid_Com_Current) AS SUM_Payables_Must_Paid_Com" +
                ",SUM(TotalTaxAgent) AS SUM_Payables_Tax_Agent" +
                " from (" + strSQL + ") as PayablesCurrentTotal";
            if ((OrderBy != null) && (OrderBy != ""))
            {
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            }

            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
            {
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            }
            //Total
            dsTotal = null;
            dsTotal = DBModule.ExecuteQuery(strSQLTotal, cn, trans);
        }
        public static void GetListTotal(string strCondition, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT  tbl_cost_accounting.IDProvince,SUM(TotalSellInvoice) AS SUM_Ref_Receivables_Must_Obtained" +
                ",SUM(Receivables_Must_Obtained) AS SUM_Receivables_Must_Obtained" +
                ",SUM(TotalComCustomer) AS SUM_Ref_Receivables_Must_Paid_Com" +
                ",SUM(Receivables_Obtained) AS SUM_Receivables_Obtained" +
                ",SUM(Receivables_Paid_Com) AS SUM_Receivables_Paid_Com" +
                ",SUM(Receivables_Must_Paid_Com) AS SUM_Receivables_Must_Paid_Com" +
                ",SUM(TotalTaxCustomer) AS SUM_Receivables_Tax_Customer " +
                //Payables
                ",SUM(TotalBuyInvoice) AS SUM_Ref_Payables_Must_Payment" +
                " ,SUM(Payables_Must_Payment) AS SUM_Payables_Must_Payment" +
                ",SUM(TotalComAgent) AS SUM_Ref_Payables_Must_Paid_Com" +
                ",SUM(Payables_Payment) AS SUM_Payables_Payment" +
                ",SUM(Payables_Paid_Com) AS SUM_Payables_Paid_Com" +
                ",SUM(Payables_Must_Paid_Com) AS SUM_Payables_Must_Paid_Com" +
                ",SUM(TotalTaxAgent) AS SUM_Payables_Tax_Agent" +
                " From tbl_Total_cost_accounting,tbl_cost_accounting" +
                " where tbl_Total_cost_accounting.CostAccountingID=tbl_cost_accounting.ID ";
            if (strCondition.Length > 0)
                strSQL += strCondition;
            strSQL += " GROUP BY tbl_cost_accounting.IDProvince ";
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }

        public static void GetListTotalFuture(string strCondition, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = " SELECT SUM(ROUND(Receivables_Must_Obtained - Receivables_Must_Paid_Com)) AS Total_Must_Obtained_Customer," +
            " SUM(ROUND(Payables_Must_Payment + Payables_Must_Paid_Com)) AS Total_Must_Payment_Agent,IdProvince" +
            " FROM `tbl_total_cost_accounting`,`tbl_cost_accounting` WHERE tbl_total_cost_accounting.CostAccountingID= tbl_cost_accounting.ID";
            if (strCondition.Length > 0)
                strSQL += strCondition;
            strSQL += " GROUP BY tbl_cost_accounting.IDProvince Order By IDProvince DESC";
            // run SQL statement\
            //GV.Response.Write(strSQL);
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }

        public void DoSearch(string strCondittion, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_Total_cost_accounting where 1=1 ";
            if (strCondittion.Length > 0)
                strSQL += strCondittion;
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static bool IsCompletePayment(long CostAccID, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "Select `ID` from tbl_Total_cost_accounting where Receivables_Must_Obtained<1 AND Receivables_Must_Paid_Com>=0 AND Receivables_Must_Paid_Com<1 AND CostAccountingID=" + CostAccID;
            string bOk = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (bOk != null)
                retVal = true;
            return retVal;
        }
    }
}


